﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetPostiledDocument]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetPostiledDocument];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetPostiledDocument]
    @StaffName nvarchar(300)
AS

DECLARE @staffid int

SELECT @staffid = staff_id 
    FROM uds_staff
    WHERE staff_name = @StaffName
    
IF @staffid>0

SELECT     a.Doc_ID,
    a.a Title,
    b.Doc_Builder_ID,
    CONVERT(nvarchar(10),Doc_Added_Date,120) AS Short_Doc_Added_Date,
    b.Doc_Status,
    b.Flow_ID,
    b.Step_ID,
    c.realname     AS DocBuilder,
    d.flow_name      AS FlowName,
    (CASE b.doc_status WHEN 0 THEN '未签' WHEN 1 THEN '待批' WHEN 2 THEN '会签中' WHEN 3 THEN '成功' WHEN 4 THEN '拒绝' end) as status,
    e.step_name     AS StepName
    FROM     
        uds_flow_style_data a,
        uds_flow_document b,
        uds_staff c,
        uds_flow d,
        uds_flow_step e
    WHERE a.Doc_ID = b.Doc_id 
        and b.flow_id         = e.flow_id
        and b.step_id         = e.step_id
        and b.flow_id         = d.flow_id
        and b.doc_builder_id     = c.staff_id
        and b.doc_id IN (
                    SELECT doc_id 
                        FROM uds_flow_postil
                        WHERE postiler_id = @staffid 
                     )

    ORDER BY Doc_Added_Date DESC